from __future__ import print_function, unicode_literals
import frappe
from frappe import _
from pypika import CustomFunction
from frappe.query_builder.functions import Count,Sum, Cast
from frappe.query_builder import Case, Criterion,Order,Interval    #from pypika import Query, Field, Table, Criterion
from frappe.utils import add_days, flt, cstr, nowdate, nowtime,rounded, get_user_date_format, add_days, today, now


@frappe.whitelist()
def get_item_plan_master_map(company):
    item = frappe.qb.DocType('Item')
    item_default = frappe.qb.DocType('Item Default')
    company_doctype = frappe.qb.DocType('Company')
    bom = frappe.qb.DocType("BOM")
    supply_method = Case().when(
                (item_default.supply_method.isnull() | item_default.supply_method ==""), item.default_material_request_type
            ).else_(
                item_default.supply_method
            ).as_('supply_method')
    safety_stock = Case().when(
                (item_default.safety_stock.isnull()), item.safety_stock
            ).else_(
                item_default.safety_stock
            ).as_('safety_stock') 
    min_order_qty = Case().when(
                (item_default.min_order_qty.isnull()), item.min_order_qty
            ).else_(
                item_default.min_order_qty
            ).as_('min_order_qty')
    lead_time_days = Case().when(
                (item_default.lead_time_days.isnull() | item_default.lead_time_days == 0), item.lead_time_days
            ).else_(
                item_default.lead_time_days + item_default.inspection_time
            ).as_('lead_time_days')
    pick_pack_days = Case().when(
                (item_default.pick_pack_days.isnull() | item_default.pick_pack_days == 0), company_doctype.pick_pack_days
            ).else_(
                item_default.pick_pack_days
            ).as_('pick_pack_days')                          
    data = frappe.qb.from_(item
        ).left_join(item_default
        ).on(item.name == item_default.parent
        ).join(company_doctype
        ).on(company_doctype.name == item_default.company
        ).left_join(bom
        ).on( (bom.item == item.name) &
               (bom.docstatus == 1) & 
               (bom.is_default == 1) & 
               (bom.company == company)
        ).select(
            item.name.as_('item_code'),
            item.item_name,
            item.stock_uom,
            item.item_group,
            item.is_sub_contracted_item,
            item_default.default_warehouse,
            item_default.default_supplier,
            item_default.planner,
            item_default.llc,
            item_default.is_dummy_item,
            item_default.ignore_existing_stock,
            supply_method,
            safety_stock,
            min_order_qty,
            lead_time_days,
            item_default.daily_capacity,
            item_default.max_order_qty,
            item_default.qty_multiple,
            #item_default.inspection_time,
            item_default.consolidate_demand_days,
            pick_pack_days,
            bom.name.as_("default_bom")
        ).where(item_default.company == company
        ).where(item.is_stock_item == 1
        ).run(as_dict = True)    
    return {d.item_code:d for d in data}

@frappe.whitelist()
def get_item_safety_stock_map(company):
    item = frappe.qb.DocType('Item')
    item_default = frappe.qb.DocType('Item Default')

    safety_stock = Case().when(
                (item_default.safety_stock.isnull()), item.safety_stock
            ).else_(
                item_default.safety_stock
            )                          
    data = frappe.qb.from_(item
        ).left_join(item_default
        ).on((item.name == item_default.parent) & (item_default.company == company)
        ).select(
            item.name.as_('item_code'),
            safety_stock.as_("safety_stock")
        ).where((item_default.safety_stock > 0) |
                (item.safety_stock > 0)
        ).where(item.is_stock_item == 1
        ).run()
    return frappe._dict(data)    

@frappe.whitelist()
def get_bom_map(company):
    fields=['name', 'item', 'quantity','`tabBOM Item`.item_code','`tabBOM Item`.item_name',
            '`tabBOM Item`.stock_qty','`tabBOM Item`.stock_uom','`tabBOM Item`.bom_no']
    filters={'docstatus':1, 'company': company}
    data_list = frappe.get_all('BOM', filters= filters, fields = fields)
    return convert_to_dict(data_list)

@frappe.whitelist()
def convert_to_dict(data_list):
    """convert list of list [[key1,value11,value12],[key2,value21,value22]] from db.sql or get_list
	 to dict {key1:[value11,value12], key2:[value21,value22]}  to improve lookup performance
    """
    data = dict.fromkeys({d[0] if type(d) in (list,tuple) else d.name for d in data_list})
    for d in data_list:
        key, value = (d[0], d[1:]) if type(d) in (list,tuple) else (d.name, d)
        if data[key] and value:
            data[key].append(value)
        else:
            data[key] = [value]
    return data

@frappe.whitelist()    
def get_item_code_onhand_map(company, warehouses = []):
    # sql = """select item_code, stock_uom,sum(actual_qty) from tabBin bin inner join tabWarehouse wh on bin.warehouse = wh.name 
    #     where wh.company = %s group by item_code, stock_uom"""
    # data = frappe.db.sql(sql,(company,), as_dict=1)
    Bin = frappe.qb.DocType('Bin')
    Warehouse = frappe.qb.DocType('Warehouse')
    query = frappe.qb.from_(Bin
            ).join(Warehouse
            ).on(Bin.warehouse == Warehouse.name
            ).select(Bin.item_code,Bin.stock_uom, Sum(Bin.actual_qty).as_("onhand")
            ).groupby(Bin.item_code, Bin.stock_uom
            ).where((Warehouse.company==company) & (Warehouse.mrp_exclude == 0)
            )
    if warehouses:
        query = query.where(Warehouse.name.isin(warehouses))
    data = query.run(as_dict = True)
    return {d.item_code:d for d in data}

@frappe.whitelist()
def get_item_code_onhand(item_code, item_code_onhand_map = {}):
        qty_dict = item_code_onhand_map.get(item_code)
        return qty_dict and (qty_dict.onhand, qty_dict.stock_uom) or (0,'')

@frappe.whitelist()
def get_open_so(warehouses, item_code_list = [], filters = {}, sort_key = None):
    """需求数量以负数表示，即 订单数量折算为库存数量后 乘以 -1
       todo:还要考虑套件销售场景，即销售明细数量在packed item表，而不是sales order item表
    """
    so = frappe.qb.DocType('Sales Order')
    so_item = frappe.qb.DocType('Sales Order Item')
    item = frappe.qb.DocType('Item')
    bom_no = Case().when(
        (so_item.mrp_bom_no.notnull() & so_item.mrp_bom_no != ''), so_item.mrp_bom_no
    ).else_(
        so_item.bom_no
    ).as_('bom_no') 
    query = frappe.qb.from_(so
            ).join(so_item
            ).on(so_item.parent == so.name
            ).join(item
            ).on(so_item.item_code == item.name
            ).select(
                Cast("Sales Order", "Char").as_('ref_doctype'),
                Cast("Sales Order", "Char").as_('process_doctype'),
                so.name.as_('ref_docname'),
                so.docstatus,
                so.transaction_date,
                so_item.name.as_("ref_doc_item"),
                so_item.idx.as_("ref_doc_item_idx"),
                so_item.item_code,
                so_item.delivery_date.as_('due_date'),
                (so_item.stock_qty * -1).as_('order_qty'),
                ((so_item.stock_qty * ((so_item.qty - so_item.delivered_qty) / so_item.qty)) * -1).as_('qty'),
                so_item.stock_uom,
                so_item.uom,
                so_item.conversion_factor,
                so_item.item_name,
                Cast('Customer','Char', alias ='partner_type'),
                so.customer.as_('partner'),
                so_item.warehouse,
                bom_no,
                so.customer
            ).where(
                Criterion.all([
                    item.is_stock_item == 1,
                    so_item.warehouse.isin(warehouses),
                    so.docstatus == 1,
                    so.status.notin(['Closed','Completed','Cancelled','On Hold']),
                    so_item.qty > so_item.delivered_qty,
                    so_item.qty > so_item.work_order_qty,
                    so_item.qty > so_item.ordered_qty
                ])
            )
    if item_code_list:
        query = query.where(
            so_item.item_code.isin(item_code_list)
        )            
    if sort_key:
        query = query.select(Cast(sort_key,"Int").as_('sort_key'))        
    if filters.get('sales_order'):
        query = query.where(so.name == filters.get('sales_order'))
    if filters.get('customer'):
        query = query.where(so.customer == filters.get('customer'))
    if filters.get('project'):
        query = query.where(so.project == filters.get('project'))
    if filters.get('warehouse'):
        query = query.where(so_item.warehouse == filters.get('warehouse'))                        
    if filters.get('item_code'):
        query = query.where(so_item.item_code == filters.get('item_code'))
    if filters.get('from_date'):
        query = query.where(so.transaction_date >= filters.get('from_date'))
    if filters.get('to_date'):
        query = query.where(so.transaction_date <= filters.get('to_date'))          
    if filters.get('from_delivery_date'):
        query = query.where(so_item.delivery_date >= filters.get('from_delivery_date'))
    if filters.get('to_delivery_date'):
        query = query.where(so_item.delivery_date <= filters.get('to_delivery_date'))    
    result = query.run(as_dict = True)
    return result

@frappe.whitelist()
def get_open_wo_gi(warehouses, item_code_list = [], filters = {}, sort_key = None, need_header_info = False):
    '''需求数量以负数表示，即 订单数量折算为库存数量后 乘以 -1
    get open reserved qty for work order'''

    wo = frappe.qb.DocType('Work Order')
    wo_item = frappe.qb.DocType('Work Order Item')
    sales_order = frappe.qb.DocType('Sales Order')
    item = frappe.qb.DocType('Item')
    uom = frappe.qb.DocType('UOM')
    pypika_floor = CustomFunction("FLOOR", ["col1"])    #qty converted to negative, here use floor instead of ceil
    #工单齐套报表，原材料剩余发料数量=需求量-已发料数量
    if need_header_info:
        gi_qty_field = wo_item.transferred_qty
    else:
        gi_qty_field = wo_item.consumed_qty
    qty_original = ((wo_item.required_qty - gi_qty_field) * -1)
    qty= Case().when(
            uom.must_be_whole_number == 1, pypika_floor(qty_original)
        ).else_(qty_original
        ).as_('qty')
    qty_where = (wo_item.required_qty > gi_qty_field)
          
    query = frappe.qb.from_(wo
            ).join(wo_item
            ).on(wo_item.parent == wo.name
            ).join(item
            ).on(wo_item.item_code == item.name
            ).join(uom
            ).on(item.stock_uom == uom.name
            ).left_join(sales_order
            ).on(sales_order.name == wo.sales_order                
            ).select(
                Cast("Work Order", "CHAR").as_('ref_doctype'),
                Cast(_("Work Order GI"), "CHAR").as_('process_doctype'),
                wo.name.as_('ref_docname'),
                gi_qty_field.as_("gi_qty"),
                wo.docstatus,
                Cast(wo.creation, "Date").as_("transaction_date"),
                wo_item.name.as_("ref_doc_item"),
                wo_item.idx.as_("ref_doc_item_idx"),
                wo_item.item_code,
                wo.production_item.as_('parent_item_code'),
                Cast(wo.planned_start_date,'Date').as_('due_date'),
                qty,
                item.stock_uom,
                item.item_name,
                wo_item.source_warehouse.as_('warehouse'),
                sales_order.customer
            ).where(
                Criterion.all([
                    wo_item.source_warehouse.isin(warehouses),
                    wo.docstatus < 2,
                    wo.status.notin(["Stopped","Completed","Closed","Cancelled"]),
                    qty_where
                ])
            ).orderby('planned_start_date')

    if need_header_info:
        query = query.select(
            wo.qty.as_('order_qty'),
            (wo.qty - wo.produced_qty).as_('open_qty'),
            wo.produced_qty,
            wo.status,
            (wo_item.required_qty / wo.qty).as_('qty_per'),
            wo.item_name.as_('parent_item_name'),
            wo.fg_warehouse,
            wo.wip_warehouse
        ) 
    if sort_key:
        query = query.select(Cast(sort_key,"Int").as_('sort_key'))             
    if item_code_list:
        query = query.where(wo_item.item_code.isin(item_code_list))
    if filters.get('production_item'):
        query = query.where(wo.production_item == filters.get('production_item'))
    if filters.get('from_date'):
        query = query.where(wo.planned_start_date >= filters.get('from_date'))
    if filters.get('to_date'):
        query = query.where(wo.planned_start_date <= filters.get('to_date')) 
    if filters.get('wo_names'):
        query = query.where(wo.name.isin(filters.get('wo_names')))       
    result = query.run(as_dict = True)
    return result

@frappe.whitelist()
def get_open_subcontract_gi(warehouses, item_code_list = [], sort_key = None, filters = {}, need_header_info = False):
    po = frappe.qb.DocType('Purchase Order')
    po_item = frappe.qb.DocType('Purchase Order Item')
    item_default = frappe.qb.DocType('Item Default')
    po_item_sup = frappe.qb.DocType('Purchase Order Item Supplied')
    item = frappe.qb.DocType('Item')
    lead_time_days = Case().when(
            (item_default.lead_time_days.isnull() | item_default.lead_time_days == 0), item.lead_time_days
        ).else_(
            item_default.lead_time_days + item_default.inspection_time
        ).as_('lead_time_days')
    due_date = Case().when(po_item.expected_delivery_date.notnull(), po_item.expected_delivery_date
        ).else_(po_item.schedule_date
        ).as_('due_date')
    query = frappe.qb.from_(po
            ).join(po_item_sup
            ).on(po.name == po_item_sup.parent
            ).join(po_item
            ).on(po_item_sup.reference_name == po_item.name
            ).join(item
            ).on(po_item_sup.rm_item_code == item.name
            ).left_join(item_default
            ).on((po_item.item_code == item_default.parent) &
                 (po.company == item_default.company)            
            ).select(
                Cast("Purchase Order","CHAR").as_('ref_doctype'),
                Cast(_("Send to Subcontractor"),"CHAR").as_('process_doctype'),
                Cast("1","INT").as_('is_subcontracted'),
                po.name.as_('ref_docname'),
                po.docstatus,
                po.transaction_date,
                po_item_sup.name.as_("ref_doc_item"),
                po_item_sup.idx.as_("ref_doc_item_idx"),
                po_item_sup.rm_item_code.as_('item_code'),
                po_item_sup.main_item_code.as_('parent_item_code'),
                (((po_item_sup.required_qty - po_item_sup.supplied_qty + po_item_sup.returned_qty)*po_item_sup.conversion_factor) * -1).as_('qty'),
                po_item_sup.stock_uom,
                due_date,
                lead_time_days,
                po_item_sup.conversion_factor,
                item.item_name,
                Cast("Supplier",'Char', alias = "partner_type"),
                po.supplier.as_("partner"),
                po_item_sup.reserve_warehouse.as_('warehouse')
            ).where(
                Criterion.all([
                    po_item_sup.reserve_warehouse.isin(warehouses),
                    po.docstatus < 2,
                    po.is_subcontracted == 'Yes',
                    po_item_sup.required_qty > po_item_sup.consumed_qty,
                    po.status.notin(["Closed", "Delivered","Completed","Cancelled"])
                ])
            )
    if need_header_info:
        query = query.select(
            po_item.qty.as_('order_qty'),
            (po_item.qty - po_item.received_qty + po_item.returned_qty).as_('open_qty'),
            (po_item.received_qty - po_item.returned_qty).as_('produced_qty'),
            (po_item_sup.required_qty / po_item.qty).as_('qty_per'),
            po_item.item_name.as_('parent_item_name'),
            po_item.warehouse.as_('fg_warehouse')
        )
        query = query.where(po.docstatus == 1)             
    if sort_key:
        query = query.select(Cast(sort_key,"Int").as_('sort_key'))             
    if item_code_list:
        query = query.where(po_item_sup.rm_item_code.isin(item_code_list))
    if filters.get('production_item'):
        query = query.where(po_item.item_code == filters.get('production_item'))
    if filters.get('from_date'):
        query = query.where(po.transaction_date >= filters.get('from_date'))
    if filters.get('to_date'):
        query = query.where(po.transaction_date <= filters.get('to_date')) 
    if filters.get('po_names'):
        query = query.where(po.name.isin(filters.get('po_names')))                  
    result = query.run(as_dict = True)
    #委外交付日期-委外物料交期天数 = 委外发料日期
    result = [frappe._dict(dict(r, **{'due_date': add_days(r.due_date, -1 * r.lead_time_days)}))
                    for r in result]
    return result        

@frappe.whitelist()
def get_open_wo(warehouses, item_code_list = [], sort_key = None):
    """生产工单保存时即检查是否超销售订单数量，所以草稿状态也算"""
    wo = frappe.qb.DocType('Work Order')
    due_date = Case().when(
                wo.expected_delivery_date.notnull(), wo.expected_delivery_date
                ).when(
                    wo.planned_end_date.notnull(), wo.planned_end_date
                ).else_(
                    wo.planned_start_date
                )
    query = frappe.qb.from_(wo
            ).select(
                Cast("Work Order","Char").as_('ref_doctype'),
                Cast("Manufacture","Char").as_('process_doctype'),
                wo.name.as_('ref_docname'),
                wo.docstatus,
                Cast(wo.creation, "Date").as_("transaction_date"),
                wo.production_item.as_('item_code'),
                wo.qty.as_('order_qty'),
                (wo.qty - wo.produced_qty).as_('qty'),
                wo.stock_uom,
                Cast(due_date,'Date').as_("due_date"),
                wo.item_name,
                wo.sales_order,
                wo.sales_order_item,
                wo.fg_warehouse.as_('warehouse')
            ).where(
                Criterion.all([
                    wo.fg_warehouse.isin(warehouses),
                    wo.docstatus < 2,
                    wo.status.notin(["Stopped", "Completed","Closed","Cancelled"]),
                    (wo.qty - wo.produced_qty) >0 
                ])
            ).orderby('planned_start_date')
    if sort_key:
        query = query.select(Cast(sort_key,"Int").as_('sort_key'))             
    if item_code_list:
        query = query.where(wo.production_item.isin(item_code_list))        
    result = query.run(as_dict = True)
    return result

@frappe.whitelist()
def get_open_po(warehouses, item_code_list = [], sort_key = None, only_expected_delivery_date = False):
    po = frappe.qb.DocType('Purchase Order')
    po_item = frappe.qb.DocType('Purchase Order Item')
    item = frappe.qb.DocType('Item')
    process_doctype = Case().when(po.is_subcontracted == 'Yes', Cast(_("Subcontract GR"),"Char")
                ).else_(Cast("Purchase Order","Char")
                ).as_("process_doctype")
    if only_expected_delivery_date:
        due_date = po_item.expected_delivery_date.as_("due_date")
    else: 
        due_date = Case().when(po_item.expected_delivery_date.notnull(), po_item.expected_delivery_date
                    ).else_(po_item.schedule_date
                    ).as_("due_date")
    query = frappe.qb.from_(po
            ).join(po_item
            ).on(po.name == po_item.parent
            ).join(item
            ).on(po_item.item_code == item.name                
            ).select(
                Cast("Purchase Order","Char").as_('ref_doctype'),
                process_doctype,
                po.name.as_('ref_docname'),
                po.docstatus,
                po.transaction_date,
                po_item.name.as_("ref_doc_item"),
                po_item.idx.as_("ref_doc_item_idx"),
                po_item.item_code,
                (po_item.stock_qty).as_('order_qty'),
                ((po_item.qty - po_item.received_qty)*po_item.conversion_factor).as_('qty'),
                po_item.stock_uom,
                due_date,
                po_item.conversion_factor,
                po_item.item_name,
                Cast("Supplier",'Char', alias = 'partner_type'),
                po.supplier.as_("partner"),
                po_item.sales_order,
                po_item.sales_order_item,
                po_item.warehouse
            ).where(
                Criterion.all([
                    item.is_stock_item == 1,
                    po_item.warehouse.isin(warehouses),
                    po.docstatus < 2,
                    po_item.qty > po_item.received_qty,
                    po.status.notin(["Closed", "Delivered"]),
                    po_item.delivered_by_supplier == 0
                ])
            )

    if sort_key:
        query = query.select(Cast(sort_key,"Int").as_('sort_key'))             
    if item_code_list:
        query = query.where(po_item.item_code.isin(item_code_list))
    if only_expected_delivery_date:
        query = query.where(po_item.expected_delivery_date.notnull()
            ).where(po_item.expected_delivery_date != "")                
    result = query.run(as_dict = True)
    return result

@frappe.whitelist()
def get_open_mr_as_forecast(warehouses, item_code_list = [], material_request_types = [], filters = {}, sort_key = 20):
    return get_open_mr(warehouses,item_code_list = item_code_list, filters = filters,
        material_request_types = ['Manufacture'], sort_key = sort_key)

@frappe.whitelist()
def get_open_mr(warehouses, item_code_list = [], material_request_types = [], filters = {}, sort_key = None):
    """Manufacture 类型作为类似销售订单的需求，即forecast,是单独处理的
    """
    mr = frappe.qb.DocType('Material Request')
    mr_item = frappe.qb.DocType('Material Request Item')
    if not material_request_types:
        material_request_types = ['Purchase', 'Customer Provided']
    process_doctype = Case().when(
        mr.material_request_type == 'Manufacture', 
        _("MR Forecast")
    ).when(mr.material_request_type == 'Material Issue', 
        _("Material Request GI")
    ).when(mr.material_request_type == 'Purchase', 
        _("Material Request Purchase")
    ).when(mr.material_request_type == 'Customer Provided', 
        _("Material Request Customer Provided")
    ).else_(_("Material Request GR")
    ).as_('process_doctype')
    qty = Case().when(
        mr.material_request_type.isin(['Manufacture','Material Issue']), 
        ((mr_item.stock_qty - mr_item.ordered_qty) * -1)
    ).else_(
        (mr_item.stock_qty - mr_item.ordered_qty)
    ).as_('qty')
    bom_no = Case().when(
        (mr_item.mrp_bom_no.notnull() & mr_item.mrp_bom_no != ''), mr_item.mrp_bom_no
    ).else_(
        mr_item.bom_no
    ).as_('bom_no')     
    query = frappe.qb.from_(mr
            ).join(mr_item
            ).on(mr.name == mr_item.parent
            ).select(
                Cast("Material Request","Char").as_('ref_doctype'),
                process_doctype,
                mr.name.as_('ref_docname'),
                mr.docstatus,
                mr.transaction_date,
                mr_item.name.as_("ref_doc_item"),
                mr_item.idx.as_("ref_doc_item_idx"),
                mr_item.item_code,
                bom_no,
                qty,
                mr_item.stock_uom,
                mr.schedule_date.as_('due_date'),
                mr_item.conversion_factor,
                mr_item.item_name,
                mr_item.uom,
                mr_item.warehouse
            ).where(
                Criterion.all([
                    mr_item.warehouse.isin(warehouses),
                    (((mr.docstatus < 2) & (mr.material_request_type == 'Customer Provided')) |
                    ((mr.docstatus == 1) & (mr.material_request_type != 'Customer Provided'))),
                    mr_item.stock_qty > mr_item.ordered_qty,
                    mr.material_request_type.isin(material_request_types),
                    mr.status.notin(['Stopped'])
                ])
            )
    if filters.get('project'):
        query = query.where(so.project == filters.get('project'))
    if filters.get('warehouse'):
        query = query.where(mr_item.warehouse == filters.get('warehouse'))                        
    if filters.get('item_code'):
        query = query.where(mr_item.item_code == filters.get('item_code'))
    if filters.get('from_date'):
        query = query.where(mr.transaction_date >= filters.get('from_date'))
    if filters.get('to_date'):
        query = query.where(mr.transaction_date <= filters.get('to_date'))          
    if filters.get('from_delivery_date'):
        query = query.where(mr_item.schedule_date >= filters.get('from_delivery_date'))
    if filters.get('to_delivery_date'):
        query = query.where(mr_item.schedule_date <= filters.get('to_delivery_date'))                 
    if sort_key:
        query = query.select(Cast(sort_key,"Int").as_('sort_key'))             
    if item_code_list:
        query = query.where(mr_item.item_code.isin(item_code_list)) 
    result = query.run(as_dict = True)
    return result

@frappe.whitelist() 
def get_open_mr_gi(warehouses, item_code_list = [], sort_key = None):
    return get_open_mr(warehouses,item_code_list = item_code_list,
        material_request_types = ['Material Issue'], sort_key = sort_key)

@frappe.whitelist()
def get_open_plan_order(company, warehouses, item_code_list = [], sort_key = None, mrp = None):        
    if not mrp:                    
        mrp = get_last_mrp(company)
            
    po = frappe.qb.DocType('Plan Order')    
    warehouses += ['']    #显示没有分派默认仓库的计划订单
    sales_order = Case().when(po.ref_doctype == "Sales Order", po.ref_docname
            ).else_(Cast("","Char")
            ).as_("sales_order")
    
    query = frappe.qb.from_(po
            ).select(
                Cast("Plan Order","Char").as_('ref_doctype'),
                Cast("Plan Order","Char").as_('process_doctype'),
                po.name.as_('ref_docname'),
                po.item_code,
                po.item_name,
                po.qty,
                po.stock_uom,
                po.due_date,
                po.warehouse,
                sales_order,
                Cast(sort_key,"Int").as_('sort_key')
            ).where(
                Criterion.all([
                    po.company == company,
                    (po.warehouse.isin(warehouses) | po.warehouse.isnull()),
                    po.convertible == 1,
                    po.converted == 0,
                    po.mrp == mrp
                ])
            )
    if item_code_list:
        query = query.where(po.item_code.isin(item_code_list))        
    result = query.run(as_dict = True)
    return result

@frappe.whitelist()
def get_open_plan_order_items(company, warehouses,item_code_list = [], sort_key = None, mrp = None):        
    if not mrp:                    
        mrp = get_last_mrp(company)            

    warehouses += ['']    #显示没有分派默认仓库的计划订单    
    po = frappe.qb.DocType('Plan Order')
    po_item = frappe.qb.DocType('Plan Order Item')
    query = frappe.qb.from_(po
            ).join(po_item
            ).on(po_item.parent == po.name 
            ).select(
                Cast("Plan Order","Char").as_('ref_doctype'),
                Cast(_("Plan Order GI"),"Char").as_('process_doctype'),
                po.name.as_('ref_docname'),
                po_item.name.as_("ref_doc_item"),
                po_item.idx.as_("ref_doc_item_idx"),
                po_item.item_code,
                po_item.item_name,
                po_item.qty,
                po_item.stock_uom,
                po_item.due_date,
                po_item.warehouse,
                Cast(sort_key,"Int").as_('sort_key')
            ).where(
                Criterion.all([
                    po.company == company,
                    po.convertible == 1,
                    po.converted == 0,
                    (po.warehouse.isin(warehouses) | po.warehouse.isnull()),
                    po.mrp == mrp
                ])
            )
    if item_code_list:
        query = query.where(po_item.item_code.isin(item_code_list))        
    result = query.run(as_dict = True)        
    return result

@frappe.whitelist()
def get_last_mrp(company):
    multi_level_mrp = frappe.qb.DocType('Multi Level MRP')
    last_mrp = frappe.qb.from_(multi_level_mrp
            ).select(multi_level_mrp.name
            ).where(
                Criterion.all([
                    ((multi_level_mrp.item_code.isnull()) | (multi_level_mrp.item_code == "")),
                    multi_level_mrp.sales_order.isnull(),
                    multi_level_mrp.from_delivery_date.isnull(),
                    multi_level_mrp.to_delivery_date.isnull(),
                    multi_level_mrp.company == company
                ])
            ).orderby(multi_level_mrp.modified, order = Order.desc     
            ).limit(1
            ).run(as_dict = True)
    return last_mrp and last_mrp[0].name or ''

@frappe.whitelist()
def get_bom_sub_items(item_code, company, sub_item_list = []):
    """用于获取BOM全部下层物料"""
    filters = {'company': company, 'docstatus': 1, 'is_default': 1, 'item': item_code}
    children = frappe.get_all('BOM', fields=['`tabBOM Item`.item_code'], filters = filters, as_list = True)
    for (child,) in children:
        if not child in sub_item_list:
            sub_item_list.append(child)
        get_bom_sub_items(child, company, sub_item_list)


@frappe.whitelist()
def get_bom_list_sub_items(bom_no_list = [], bom_map = {}, processed_bom_set = {}, sub_item_set = {}):
    for bom_no in bom_no_list:
        get_bom_no_sub_items(bom_no, bom_map, processed_bom_set, sub_item_set)

@frappe.whitelist()
def get_bom_no_sub_items(bom_no, bom_map = {}, processed_bom_set = {}, sub_item_set = {}):
    """retrieve all bom's child item_codes"""
    processed_bom_set.add(bom_no)
    filters = {'parent': bom_no, 'docstatus': 1}
    if not bom_map:
        children = frappe.get_all('BOM Item', fields=['item_code', 'bom_no'], filters = filters)
    else:
        children = bom_map.get(bom_no, [])
    for child in children:
        child_item_code, child_bom_no = child.item_code, child.bom_no
        sub_item_set.add(child_item_code)
        if not child_bom_no in processed_bom_set:
            get_bom_no_sub_items(child_bom_no, bom_map, processed_bom_set, sub_item_set)


"""
from zelin_am.utils import *
sql = "select name from tabBOM where company = 'frappedemo' and name not in (select bom_no from `tabBOM Item`)"
top_bom_no_list = frappe.db.sql(sql, as_list = 1)
processed_bom_set = set()
sub_item_set = set()
bom_map1 = {}
get_bom_list_sub_items([b[0] for b in top_bom_no_list], bom_map1,processed_bom_set, sub_item_set)
bom_map = get_bom_map('frappedemo')
processed_bom_set1 = set()
sub_item_set1 = set()
get_bom_list_sub_items([b[0] for b in top_bom_no_list], bom_map,processed_bom_set1, sub_item_set1)
"""